import pymysql

class DB:
    # 初始化连接
    def __init__(self, host, user, password, database, port=3306, charset='utf8'):
        self.con = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database,
            port=port,
            charset=charset
        )
        self.cursor = self.con.cursor()  # 初始化 cursor 属性

    # 游标
    def get_cursor(self):
        return self.con.cursor()

    # 创建数据库
    def create_db(self, sql):
        try:
            cursor = self.get_cursor()
            cursor.execute(sql)
            self.con.commit()
            print("创建成功")
        except Exception as e:
            print("创建失败", e)
            self.con.rollback()
            return False
        finally:
            cursor.close()  # 只关闭 cursor，不关闭 connection
    # 关闭数据库连接
    def close(self):
        self.con.close()

    # 数据库的更新
    def update(self, sql, params=None):
        try:
            cursor = self.get_cursor()
            cursor.execute(sql, params)
            self.con.commit()
            print('数据更新成功！')
        except Exception as e:
            print('数据更新失败！', e)
            self.con.rollback()
        finally:
            cursor.close()  # 只关闭 cursor，不关闭 connection
    # 数据库的查询
    def select(self, sql, params=None):
        try:
            cursor = self.get_cursor()
            cursor.execute(sql, params)
            data = cursor.fetchall()
            return data
        except Exception as e:
            print('数据查询失败！', e)
        finally:
            cursor.close()  # 只关闭 cursor，不关闭 connection

if __name__ == '__main__':
    # 创建 DB 实例，连接到 MySQL 服务器但不指定数据库
    db = DB(host='127.0.0.1', user='root', password='123456', database='')

    # 判断数据库是否存在
    sql = "SHOW DATABASES LIKE 'person'"
    result = db.select(sql)
    if result:
        print('数据库已存在')
    else:
        create_sql = "CREATE DATABASE person"
        db.create_db(create_sql)

    # 重新连接到新创建的数据库
    db = DB(host='127.0.0.1', user='root', password='123456', database='person')

    # 创建表
    create_table_sql = """
    CREATE TABLE IF NOT EXISTS person (
        username varchar(20),
        age int,
        sex char(4),
        high double(3, 2)
    )
    """
    db.update(create_table_sql)

    # 循环输入10个人的信息（姓名，年龄，性别，身高），并存储到数据库中
    for _ in range(10):
        # 输入信息
        username = input("请输入姓名: ")
        age = int(input("请输入年龄: "))
        sex = input("请输入性别: ")
        high = float(input("请输入身高: "))

        insert_sql = """
        INSERT INTO person (username, age, sex, high)
        VALUES (%s, %s, %s, %s)
        """
        db.update(insert_sql, (username, age, sex, high))
    # 关闭数据库连接
    db.close()
